数据库笔记（八）——视图 、事务

写在前面：本篇博客大部分内容参考数据库系统概念（本科教学版）第四章(第三章的多表部分会挪到这一部分讲)
笔者接下来的代码示例会主要在SQL Server数据库中测试

在开始今天的摸鱼大业之前，让我们继续延用之前用的表(´`)

视图
首先还是来谈谈笔者对视图概念的理解吧，在笔者看来视图这个数据库对象在不考虑物化的情况下就是一张虚表，本身没有实际的数据，但是可以利用视图在某个视角下观察一张或多张表的数据。所有对视图的操作最后都将转成对表的操作

视图定义（创建一个视图）

格式：
1
CREATE VIEW 视图名 AS 子查询

举个栗子：

-- 下面的语句创建了一个简单的视图temp_view，其中包含员工名以及其所在部门名两个字段
CREATE VIEW temp_view AS
SELECT e.ENAME, d.DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

接下来我们对上面创建的视图进行查询操作
1
2
SELECT *
FROM temp_view;

得到如下结果：

数据库只存储视图的定义本身

视图通常是这样来实现的：当我们定义一个视图的时候，数据库系统就吧这个视图的定义（其实就是一个子查询语句）本身存储下来，一旦我们使用这个视图，数据库系统就会用定义中的查询语句替换视图

举个栗子

-- 我们继续沿用上面定义的视图temp_view，它的定义是这样的
CREATE VIEW temp_view AS
SELECT e.ENAME, d.DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO;

-- 则对视图的查询操作可以作下面的等价(下面两个查询语句是等价的)
SELECT *
FROM temp_view;

SELECT *
FROM (SELECT e.ENAME, d.DNAME
FROM EMP e JOIN DEPT d ON e.DEPTNO = d.DEPTNO) as temp_view;

可更新的视图
- 条件
  1. FROM子句中只有一个数据库关系（即在定义视图的时候，后面的SELECT语句中只能查询一个表）
  - SELECT子句中只包含关系的属性名，不包含任何的表达式，集聚函数或distinct声明
  - 任何没有出现在SELECT子句中的属性可以取空值（即这些属性既没有not null 约束，也不构成主码的一部分）
  - 查询中不含有group by 或 having 子句
- 满足上述条件的视图我们称作可更新的视图，在可更新视图上允许执行update、insert、delete操作
- 视图是在表的基础上定义的，所有对视图的操作最后都将作用在表上

视图的update、insert、delete

先定义一个视图，值包括员工的职工号、名字和工资
1
2
3
CREATE VIEW emp_info AS
SELECT EMPNO, ENAME, SAL
FROM EMP;
视图查询的结果如下：

update

举个栗子

-- 下面的语句更新SMITH的工资
UPDATE emp_info
SET SAL = 900
WHERE ENAME = 'SMITH'

--接下来查询表中的数据是否受到影响（查询的结果是900，就不贴图了）
SELECT ENAME, SAL
FROM EMP
WHERE ENAME = 'SMITH';

insert

举个栗子

-- 往视图中插入一条数据（下面插入是可以执行成功的，可以自己试试，创建一个视图，只包含员工名字和工资，不包含EMPNO，这样就不能执行插入语句，违反了上面四个条件中的第三个条件）
INSERT INTO emp_info (EMPNO, ENAME, SAL) VALUES (
    8888, 'Robbin', '1000'
);

–查询一下原表中的数据，会发现多了下面这条

视图的插入操作，最终作用到表上，并且是给视图中有的字段赋值，没有的字段都赋null值（这就解释了上面可更新视图的四个条件中的条件3）

delete

举个栗子

1
2
3

-- 执行结果就是原表中的数据也被删除了
DELETE emp_info
WHERE emp_info.EMPNO = 8888

物化视图
- 就是保存视图的结构，再使用时直接用保存的结果表，而不用再去将视图替换成定义，再次执行一遍查询视图的操作。这个在对视图的操作很频繁的时候是对效率很有增益的，但是同时带来的是同步的问题。就是原表中的数据更新了，但是视图物化的数据却没有更新，导致数据不一致。
- 一般用的很少，常用于数据仓库
视图更新检查（WITH CHECK OPTION）
- 默认情况下，如果一个视图满足以上可更新视图的定义，则通常的更新操作是允许被执行的
- 但是如果在创建视图的时候，在后面跟上 WITH CHECK OPTION,那么对视图的每一个更新操作，都会检查，更改后的结果，是否满足创建视图时WHERE子句中的限定。
- 举个栗子：
  1
  2
  3
  4
  5
  6
  -- 我们在上面定义视图的栗子基础上改造一下，并加上WITH CHECK OPTION
  CREATE VIEW emp_info AS
  SELECT EMPNO, ENAME, SAL
  FROM EMP
  WHERE SAL > 3000
  WITH CHECK OPTION;
  - 上面视图的定义就是包含了工资超过3000的所有员工的员工号，名字，以及工资
  - 这个时候如果我们执行下面的更新操作
    1
    2
    3
    4
    -- 下面这个操作将会被拒绝执行
    UPDATE emp_info
    SET SAL = 2000
    WHERE ...
  - 由于在定义视图的时候添加了 WITH CHEKC OPTION 选项，所以在执行更新操作的时候回检查是否符合创建视图时WHERE子句中的条件，显然上面的操作不满足，故被拒绝执行。
    
    事务
一个事务就是一个原子的，不可分割的任务，里面可能包含多条SQL语句。在事务机制下，一个事务中的多条SQL语句要么都执行，要么都不执行。
性质
- 原子性
  - 事务是原子的，里面包含的一条或多条SQL语句要么都执行，要么都不执行
- 一致性
  - 在隔离的执行多个事务时，用同步锁保证对数据库数据的一致性访问
- 隔离性
  - 多个事务并行执行，若其中一个事务还没有结束，其他事务内是无法观察到这个事务对数据库的影响的
- 持久性
  - 一旦事务正常完成，并提交，则其对数据库的修改就会持久化到数据库内。
事务的开始和终止
- 虽然各种关系型数据库都支持事务，但是不同的数据库的实现不同。具体的事务的开始和终止有隐式和显示之分
- 事务的开始
  - 显示：begin transition （例如SQL Server数据库）
  - 隐式：第一条DML语句（增删改查语句）开始执行就会开启一个事务（例如Oracle数据库）
- 事物的终止
  - 显示： end transition (例如SQL Server数据库)
  - 隐式：执行 rollback、commit或者一条DDL语句（例如Oracle数据库）
数据库加锁的范围不同
- 表级锁
- 页级锁
- 行级锁

举个栗子(由于使用SQL Server做的测试，所以采用显示的事务开启和关闭)

-- 首先查看博客开头ALLEN的工资是1600

-- 下面开启一个事务，并执行一条涨工资的操作（执行会立即完成）
BEGIN TRANSACTION

UPDATE EMP SET SAL = SAL * 2
WHERE ENAME = 'ALLEN';

-- 与此同时，开启另一个事务，执行一条涨工资的操作（语句没有执行，一直在等待。这是因为上面那个事务对ALLEN的数据进行操作了，并且事务没有结束，由于同步锁的缘故，所以本事务中的修改语句要等上面那个事务执行完才能继续执行）
BEGIN TRANSACTION

UPDATE EMP SET SAL = SAL * 3
WHERE ENAME = 'ALLEN';

-- 如果此时结束第一个事务，那么第二个事务的语句就能继续执行，此时提交第二个事务，最后得到的结果是，ALLEN的工资为9600
COMMIT

完整性约束

主键约束
外键约束（参照完整性约束）
单关系上的约束
- not null
- unique
- check(<谓词>)

数据库笔记（八）——视图、事务

视图

视图定义（创建一个视图）

数据库只存储视图的定义本身

可更新的视图

条件

满足上述条件的视图我们称作可更新的视图，在可更新视图上允许执行update、insert、delete操作

视图是在表的基础上定义的，所有对视图的操作最后都将作用在表上

视图的update、insert、delete

update

insert

delete

物化视图

视图更新检查（WITH CHECK OPTION）

事务

性质

事务的开始和终止

事务的开始

事物的终止

数据库加锁的范围不同

举个栗子(由于使用SQL Server做的测试，所以采用显示的事务开启和关闭)

完整性约束

主键约束

外键约束（参照完整性约束）

单关系上的约束